import  pandas  as pd
import openpyxl
from openpyxl.styles import Font, colors, Alignment
import os
import shutil
import numpy as np
import time


print("汇总开始")


isExists=os.path.exists('./临时文件/单位汇总/')
if not isExists:

    os.makedirs('./临时文件/单位汇总/')  #创建中间文件路径
    os.makedirs('./临时文件/系统汇总/')
    os.makedirs('./临时文件/产品汇总/')
else:
    print("目录已存在，进行下一步汇总")

rootpath = './数据集'

dw_goalpath = './临时文件/单位汇总/'
xt_goalpath = './临时文件/系统汇总/'
cp_goalpath = './临时文件/产品汇总/'
sum_goalpath= './汇总表.xlsx'

starttime = time.time() #记录开始时间
data = np.array([])  #存放每次单位信息
xt_num=0           #系统数目
dw_num=0           #单位数目


     #   print('该文件夹已存在，无法创建')   # 创建文件时如果路径不存在会创建这个路径

#=============================遍历一级目录 ====================================

file_1_list = os.listdir(rootpath)   #获取数据下的全部目录信息

for file in file_1_list:         #第一级的路径（每个单位文件）

    file_1_path =os.path.join(rootpath, file) 
    file_2_list=os.listdir(file_1_path)
   
#=============================遍历二级目录 寻找第二级文件====================================

    for file1 in file_2_list:
        file_2_path = os.path.join(file_1_path,file1)   #第二级的路径（dw.xls + xt目录）
       
        if os.path.isfile(file_2_path):    #判断第二级目录下是否为文件
            print(file_2_path)

            dw_num = dw_num+1
            f = open(file_2_path,'rb') #打开
            sheetName1='单位基本情况'  #读取sheet的名字
            df = pd.read_excel(f,sheet_name=sheetName1)
            data = df.values  # 获取所有的数据
            
#========================================写入 单位 中间文件====================================
            
            wb = openpyxl.Workbook() # 创建新的表格
            sheet = wb.active   # 利用当前活动的sheet
            sheet.title="产品列表"  #如果是文件 
            sheet['C4'] = data[4,2]    #所属省部
            sheet['D4'] = data[4,5]   #归口密码管理部门
            sheet['E4'] = data[0,2]  #单位名称
            sheet['F4'] = data[1,2]  #组织机构代码
            sheet['G4'] =data[3,2]+data[3,3]  #单位地址
            sheet['H4'] =data[6,3]  #单位负责人姓名
            sheet['I4'] =data[6,5]  #单位负责人姓名
            sheet['J4'] =data[7,3]  #单位负责人办公电话'
            sheet['K4'] = data[7,5]  #单位负责人电子邮件
            sheet['L4'] =data[7,5]  #责任部门
            sheet['M4'] = data[9,3]  #责任部门联系人姓名
            sheet['N4'] =data[9,5]    #责任部门联系人职务/职称'
            sheet['O4'] =  data[10,3]  # 责任部门联系人办公电话
            sheet['P4'] = data[11,3]   #责任部门联系人移动电话
            sheet['Q4'] = data[10,5]   # 责任部门联系人电子邮件
            sheet['R4'] =   data[1,2]   #  单位类型
            sheet['S4'] = data[5,2]  # 行业主管部门
            sheet['T4'] =data[19,3]  #网络和信息系统总数
            sheet['U4'] =data[19,5]   #未使用密码的系统数量
            sheet['V4'] = data[20,3]   #使用国外密码的系统数量
            if data[12,3] == "1.已明确" : #判断是否有明确负责密码使用的管理的内设机构
                sheet['W4'] = data[12,5]   #负责密码使用管理的内设机构名称
                sheet['X4'] = data[13,3]   #负责密码使用管理的内设机构负责人
                sheet['Y4'] = data[13,5]   #负责密码使用管理的内设机构职务
                sheet['Z4'] = data[14,3]   #负责密码使用管理的内设机构联系人
                sheet['AA4'] = data[14,5]  #负责密码使用管理的内设机构联系人电话
        
            else :
                sheet['W4'] = '未明确'
                sheet['X4'] = '未明确'
                sheet['Y4'] = '未明确'
                sheet['Z4'] = '未明确'
                sheet['AA4'] = '未明确'  
        
            if  data[15,3] == '1.有':  #判断是否有经费投入
                sheet['AB4'] = data[15,5]   #密码设备及服务投入经费预算
        
            else :
                sheet['AB4'] = '无'       #密码设备及服务投入经费预算
                sheet['AC4'] =data[22,5]   #使用密码的产品综合情况密码的产品总数
                sheet['AD4'] =data[21,3]    #使用密码的产品综合情况取得国密管审批型号数量
                sheet['AE4'] = data[21,5]   #使用密码的产品综合情况未取得审批型号国内产品数量
                sheet['AF4'] = data[22,3]   # 未取得审批型号国内产品数量国外产品数量
               
                #print(datetime.datetime(data[16,3]))
                sheet['AG4'] = data[16,2]  #填报日期
            
            wb.save(dw_goalpath+'dw_'+str(dw_num)+'.xlsx')   ###存放每次单位中间文件
            f.close() 
             
#=============================遍历二级  寻找第三级目录====================================
    
    for file1 in file_2_list:          
        
        file_2_path = os.path.join(file_1_path,file1) #第二级的路径（dw.xls + xt目录）
        
        if os.path.isdir(file_2_path): #如果判断第二级是目录，去第三级循环
        
            file_3_list = os.listdir(file_2_path) 
            
#=============================遍历三级 找xt——excel====================================          
            
            for file2 in file_3_list: 
                file_31_path= os.path.join(file_2_path,file2)
                print(file_31_path)
                
                xt_num=xt_num+1
                
                
#===============================打开xt 文件====================================
                f1 = open(file_31_path,'rb') #打开
                sheetName1='应用系统基本情况'  #读取sheet的名字
                df1 = pd.read_excel(f1,sheet_name=sheetName1)
                data1 = df1.values  # 获取所有的数据
           
                sheetName2='使用的密码产品情况'  #读取sheet的名字
                df2 = pd.read_excel(f1,sheet_name=sheetName2)
                data2 = df2.values  # 获取所有的数据

                sheetName3='密码应用统计（自动）'  #读取sheet的名字
                df3 = pd.read_excel(f1,sheet_name=sheetName3)
                data3 = df3.values  # 获取所有的数据
                
                f1.close()
                
#========================================写入 系统 中间文件====================================
                
                wb = openpyxl.Workbook() # 创建新的表格
                sheet_xt= wb.active   # 利用当前活动的sheet
                sheet_xt.title= '系统列表'
                
                sheet_xt['B4'] = 1  #序号
                sheet_xt['C4'] = data[4,2]   #所属省部
                sheet_xt['D4'] = data[4,5]    #归口密码管理部门
                sheet_xt['E4'] = data1[0,2]   #系统所属单位
                sheet_xt['F4'] = data1[1,2]   #网络或系统名称
                sheet_xt['G4'] = data1[1,4]   #等级保护定级情况
                if data1[2,2] == '是':  #判断是否备案
                    sheet_xt['H4'] =data1[2,4] #得到备案编号
                else :
                    sheet_xt['H4']= "无"
    
                if data1[3,2]=='是':  # 判断是否认定为关键信息基础设施
                    sheet_xt['I4'] =data1[3,4]  # 关键信息基础设施
                else:
                    sheet_xt['I4']= '无'        
                sheet_xt['J4'] =data1[4,2]   #系统建设实施主体分类
                sheet_xt['K4'] =data1[4,4]   #系统建设实施主体分类
                sheet_xt['L4'] =data1[10,2]   #功能描述
                sheet_xt['M4'] = data1[5,2]  #系统服务情况服务范围
                sheet_xt['N4'] = data1[6,2]  #系统服务情况服务领域
                sheet_xt['O4'] = data1[8,2]  #系统服务情况服务对象
                sheet_xt['P4'] =data1[7,2]  #网络覆盖情况
                sheet_xt['Q4'] =data1[6,4]  #系统用户数量
                sheet_xt['R4'] =data1[9,4]  #投入运行时间
                sheet_xt['S4'] =data1[11,4]   #系统集成外包单位名称（若有）
                sheet_xt['T4'] =data1[12,4]  #系统集成外包单位名称（若有）
                sheet_xt['U4'] = data1[13,4]  #测评机构名称
                sheet_xt['V4'] = data1[14,2] #证书编号
                sheet_xt['W4'] = data1[14,4]  #测评时间
                sheet_xt['X4'] = data1[15,4]  #系统密码应用安全性评估情况 测评机构名称
                sheet_xt['Y4'] =data1[16,2]  #系统密码应用安全性评估情况 证书编号
                sheet_xt['Z4'] = data1[16,4] #系统密码应用安全性评估情况 测评时间
               
                sheet_xt['AA4'] = ' '  #系统密码应用安全性评估情况 测评报告编号
                
                sheet_xt['AB4'] = data3[2,3]  #系统使用密码产品数量
                sheet_xt['AC4'] = data3[2,4]  #独立使用数量
                sheet_xt['AD4'] = data3[2,5]  #共享使用数量
                sheet_xt['AE4'] = data3[2,6]  #取得国密局审批型号产品数量
                sheet_xt['AF4'] = data3[2,7]  #未取得国密局审批型号的国内产品数量
                sheet_xt['AG4'] = data3[2,8]   #国外产品数量
                sheet_xt['AH4'] = data3[2,10]  #系统中使用的密码算法
                sheet_xt['AI4'] = data3 [2,2]   #填报日期
                
                wb.save(xt_goalpath+'xt_'+str(xt_num)+'.xlsx')
                
#========================================写入 产品 中间文件====================================
                wb = openpyxl.Workbook() # 创建新的表格
                sheet_cp = wb.active   # 利用当前活动的sheet
                sheet_cp.title="产品列表"  
                for i in range(2,len(data2)):
                    
    
                    a= data2[i,1]  #查看序号
                    b= data2[i,2]  #查看产品信息
                    c= data2[i,3] 
                    d= data2[i,4]
                    e= data2[i,5]
                     
                    if (str(a) !='nan')  and (str(b) !='nan') and (str(c)!='nan') and (str(d) !='nan') and (str(e)!='nan') :
            
                        sheet_cp.cell(row=i+2,column=2,value=data2[i,1]) #序号
                        sheet_cp.cell(row=i+2,column=3,value=data[4,2]) #所属省部
                        sheet_cp.cell(row=i+2,column=4,value=data[4,5])  #归口密码管理部门
                        sheet_cp.cell(row=i+2,column=5,value=data2[0,3]) #所属单位
                        sheet_cp.cell(row=i+2,column=6,value=data2[0,10]) #应用系统
                        sheet_cp.cell(row=i+2,column=7,value=data2[i,2])  #密码产品名称
                        sheet_cp.cell(row=i+2,column=8,value=data2[i,3])  #产品类别
                        sheet_cp.cell(row=i+2,column=9,value=data2[i,4])  #型号
                        sheet_cp.cell(row=i+2,column=10,value=data2[i,6])  #生产厂家
                        sheet_cp.cell(row=i+2,column=11,value=data2[i,11])  #是否共享使用
                        sheet_cp.cell(row=i+2,column=12,value=data2[i,7])  #数量(台/套)
                        sheet_cp.cell(row=i+2,column=13,value=data2[i,9])  #商密型号
                        sheet_cp.cell(row=i+2,column=14,value=data2[i,12])  #使用的密码算法
                        sheet_cp.cell(row=i+2,column=15,value=data2[i,10])  #主要用途
                        
                    else:
                        break
                    
                wb.save(cp_goalpath+'cp_'+str(xt_num)+'.xlsx')
        
# 设计单位表格
#=============================================================================
def create_dw():
    
    #######设置行高
    sheet.row_dimensions[1].height = 25   # 第1行行高
    sheet.row_dimensions[2].height = 15   # 第2行行高
    sheet.row_dimensions[3].height = 25   # 第3行行高

    ########## 设置列宽
    sheet.column_dimensions['A'].width = 3
    sheet.column_dimensions['B'].width = 6  # B列列宽
    sheet.column_dimensions['C'].width = 15  # C列列宽
    sheet.column_dimensions['D'].width = 18  # C列列宽
    sheet.column_dimensions['E'].width = 15  # C列列宽
    sheet.column_dimensions['F'].width = 15  # C列列宽
    sheet.column_dimensions['G'].width = 21  # C列列宽
    sheet.column_dimensions['H'].width = 9  # C列列宽
    sheet.column_dimensions['I'].width = 10  # C列列宽
    sheet.column_dimensions['J'].width = 10  # C列列宽
    sheet.column_dimensions['K'].width = 11  # C列列宽
    sheet.column_dimensions['L'].width = 11  # C列列宽
    sheet.column_dimensions['M'].width = 9  # C列列宽
    sheet.column_dimensions['N'].width = 9  # C列列宽
    sheet.column_dimensions['O'].width = 9  # C列列宽
    sheet.column_dimensions['P'].width = 9 # C列列宽
    sheet.column_dimensions['Q'].width = 9  # C列列宽
    sheet.column_dimensions['R'].width = 9 # C列列宽
    sheet.column_dimensions['S'].width = 12 # C列列宽
    sheet.column_dimensions['T'].width = 9  # C列列宽
    sheet.column_dimensions['U'].width = 9  # C列列宽
    sheet.column_dimensions['V'].width = 9  # C列列宽
    sheet.column_dimensions['W'].width = 9  # C列列宽
    sheet.column_dimensions['X'].width = 9  # C列列宽
    sheet.column_dimensions['Y'].width = 9  # C列列宽
    sheet.column_dimensions['Z'].width = 9  # C列列宽
    sheet.column_dimensions['AA'].width = 9  # C列列宽
    sheet.column_dimensions['AB'].width = 12  # C列列宽
    sheet.column_dimensions['AC'].width = 12  # C列列宽
    sheet.column_dimensions['AD'].width = 18  # C列列宽
    sheet.column_dimensions['AE'].width = 22  # C列列宽
    sheet.column_dimensions['AF'].width = 11  # C列列宽
    sheet.column_dimensions['AG'].width = 9  # C列列宽
#######################################################################
    sheet.merge_cells('B1:C1')
    sheet['B1'] ='单位情况统计表：'
    sheet['B1'].alignment = Alignment(horizontal='center', vertical='center')
    sheet['B1'] ='单位情况统计表：'
    
    #bold指是否加粗,italic指是否倾斜
    songti_font = Font(name='宋体', size=12, italic=False, color=colors.BLACK, bold=True)
    sheet['B1'].font = songti_font
    
    
    sheet.merge_cells('B2:B3')
    sheet['B2'] ='序号'
    sheet['B2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['B2'].font = songti_font
    
   
    sheet.merge_cells('C2:C3')
    sheet['C2'] ='所属省部'
    sheet['C2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['C2'].font = songti_font
      
    
    sheet.merge_cells('D2:D3')
    sheet['D2'] ='归口密码管理部门'
    sheet['D2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['D2'].font = songti_font
 
    
    sheet.merge_cells('E2:E3')
    sheet['E2'] ='单位名称'
    sheet['E2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['E2'].font = songti_font
   
    
    sheet.merge_cells('F2:F3')
    sheet['F2'] ='组织机构代码'
    sheet['F2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['F2'].font = songti_font
  
    
    sheet.merge_cells('G2:G3')
    sheet['G2'] ='单位地址'
    sheet['G2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['G2'].font = songti_font
 
    sheet.merge_cells('H2:K2')
    sheet['H2'] ='单位负责人'
    sheet['H2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['H2'].font = songti_font
    
    sheet['H3'] ='姓名'
    sheet['H3'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['H3'].font = songti_font
    
    
    sheet['I3'] ='职务/职称'
    sheet['I3'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['I3'].font = songti_font
  
    
    sheet['J3'] ='办公电话'
    sheet['J3'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['J3'].font = songti_font
 
    sheet['K3'] ='电子邮件'
    sheet['K3'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['K3'].font = songti_font
  
    
    sheet.merge_cells('L2:L3')
    sheet['L2'] ='责任部门'
    sheet['L2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['L2'].font = songti_font
    

    
    sheet.merge_cells('M2:Q2')
    sheet['M2'] ='责任部门联系人'
    sheet['M2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['M2'].font = songti_font
    
    
    sheet['M3'] ='姓名'
    sheet['M3'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['M3'].font = songti_font
  
    
    sheet['N3'] ='职务/职称'
    sheet['N3'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['N3'].font = songti_font
  
    
    
    sheet['O3'] ='办公电话'
    sheet['O3'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['O3'].font = songti_font
    
    
    sheet['P3'] ='移动电话'
    sheet['P3'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['P3'].font = songti_font
 
    
    sheet['Q3'] ='电子邮件'
    sheet['Q3'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['Q3'].font = songti_font
    
    
    
    sheet.merge_cells('R2:R3')
    sheet['R2'] ='单位类型'
    sheet['R2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['R2'].font = songti_font
 
    
    sheet.merge_cells('S2:S3')
    sheet['S2'] ='行业主管部门'
    sheet['S2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['S2'].font = songti_font
   
    
    sheet.merge_cells('T2:V2')
    sheet['T2'] ='网络和信息系统总数'
    sheet['T2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['T2'].font = songti_font
    
    sheet['T3'] ='采用或部分国产密码的系统数量'
    sheet['T3'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['T3'].font = songti_font
   
    
    sheet['U3'] ='未使用密码的系统数量'
    sheet['U3'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['U3'].font = songti_font
    
    
    sheet['V3'] ='使用国外密码的系统数量'
    sheet['V3'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['V3'].font = songti_font
   
    
    sheet.merge_cells('W2:AA2')
    sheet['W2'] ='负责密码使用管理的内设机构'
    sheet['W2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['W2'].font = songti_font
        
    sheet['W3'] ='机构名称'
    sheet['W3'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['W3'].font = songti_font
    
    sheet['X3'] ='负责人'
    sheet['X3'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['X3'].font = songti_font
       
    sheet['Y3'] ='职务'
    sheet['Y3'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['Y3'].font = songti_font
       
    sheet['Z3'] ='联系人'
    sheet['Z3'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['Z3'].font = songti_font
      
    sheet['AA3'] ='电话'
    sheet['AA3'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['AA3'].font = songti_font
        
    sheet.merge_cells('AB2:AB3')
    sheet['AB2'] ='密码设备及服务投入经费预算'
    sheet['AB2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['AB2'].font = songti_font
   
        
    sheet.merge_cells('AC2:AF2')
    sheet['AC2'] ='使用密码的产品综合情况'
    sheet['AC2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['AC2'].font = songti_font
    
    
    sheet['AC3'] ='密码的产品总数'
    sheet['AC3'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['AC3'].font = songti_font
   
    
    sheet['AD3'] ='取得国密管审批型号数量'
    sheet['AD3'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['AD3'].font = songti_font

    
    sheet['AE3'] ='未取得审批型号国内产品数量'
    sheet['AE3'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['AE3'].font = songti_font

    sheet['AF3'] ='国外产品数量'
    sheet['AF3'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['AF3'].font = songti_font
   
    sheet.merge_cells('AG2:AG3')
    sheet['AG2'] ='填报日期'
    sheet['AG2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['AG2'].font = songti_font
    
def create_xt():
    
    #######设置行高
    sheet.row_dimensions[1].height = 25   # 第1行行高
    sheet.row_dimensions[2].height = 15   # 第2行行高
    sheet.row_dimensions[3].height = 25   # 第3行行高

    ########## 设置列宽
    sheet.column_dimensions['A'].width = 3
    sheet.column_dimensions['B'].width = 6  # B列列宽
    sheet.column_dimensions['C'].width = 15  # C列列宽
    sheet.column_dimensions['D'].width = 18  # C列列宽
    sheet.column_dimensions['E'].width = 15  # C列列宽
    sheet.column_dimensions['F'].width = 15  # C列列宽
    sheet.column_dimensions['G'].width = 21  # C列列宽
    sheet.column_dimensions['H'].width = 17  # C列列宽
    sheet.column_dimensions['I'].width = 14  # C列列宽
    sheet.column_dimensions['J'].width = 17  # C列列宽
    sheet.column_dimensions['K'].width = 20  # C列列宽
    sheet.column_dimensions['L'].width = 18  # C列列宽
    sheet.column_dimensions['M'].width = 9  # C列列宽
    sheet.column_dimensions['N'].width = 9  # C列列宽
    sheet.column_dimensions['O'].width = 9  # C列列宽
    sheet.column_dimensions['P'].width = 10 # C列列宽
    sheet.column_dimensions['Q'].width = 12  # C列列宽
    sheet.column_dimensions['R'].width = 10 # C列列宽
    sheet.column_dimensions['S'].width = 12 # C列列宽
    sheet.column_dimensions['T'].width = 12  # C列列宽
    sheet.column_dimensions['U'].width = 9  # C列列宽
    sheet.column_dimensions['V'].width = 9  # C列列宽
    sheet.column_dimensions['W'].width = 9  # C列列宽
    sheet.column_dimensions['X'].width = 11  # C列列宽
    sheet.column_dimensions['Y'].width = 9  # C列列宽
    sheet.column_dimensions['Z'].width = 9  # C列列宽
    sheet.column_dimensions['AA'].width = 11  # C列列宽
    sheet.column_dimensions['AB'].width = 15  # C列列宽
    sheet.column_dimensions['AC'].width = 9  # C列列宽
    sheet.column_dimensions['AD'].width = 10  # C列列宽
    sheet.column_dimensions['AE'].width = 21  # C列列宽
    sheet.column_dimensions['AF'].width = 28  # C列列宽
    sheet.column_dimensions['AG'].width = 13  # C列列宽
    sheet.column_dimensions['AH'].width = 62  # C列列宽
    sheet.column_dimensions['AH'].width = 20
    
    sheet.merge_cells('B1:D1')
    sheet['B1'] ='网络和信息系统密码应用情况统计表：'
    sheet['B1'].alignment = Alignment(horizontal='center', vertical='center')  #居中
    songti_font = Font(name='宋体', size=12, italic=False, color=colors.BLACK, bold=True)
    sheet['B1'].font = songti_font
    

    sheet.merge_cells('B2:B3')
    sheet['B2'] ='序号'
    sheet['B2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['B2'].font = songti_font
   
    sheet.merge_cells('C2:C3')
    sheet['C2'] ='所属省部'
    sheet['C2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['C2'].font = songti_font

    sheet.merge_cells('D2:D3')
    sheet['D2'] ='归口密码管理部门'
    sheet['D2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['D2'].font = songti_font
   
    
    sheet.merge_cells('E2:E3')
    sheet['E2'] ='系统所属单位'
    sheet['E2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['E2'].font = songti_font
 
    
    sheet.merge_cells('F2:F3')   
    sheet['F2'] ='网络或系统名称'
    sheet['F2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['F2'].font = songti_font
  
    
    sheet.merge_cells('G2:G3')
    sheet['G2'] ='等级保护定级情况'
    sheet['G2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['G2'].font = songti_font

    sheet.merge_cells('H2:H3')
    sheet['H2'] ='等级保护定级备案编号'
    sheet['H2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['H2'].font = songti_font
    
    sheet.merge_cells('I2:I3')
    sheet['I2'] ='关键信息基础设施'
    sheet['I2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['I2'].font = songti_font
      
    sheet.merge_cells('J2:J3')
    sheet['J2'] ='系统建设实施主体分类'
    sheet['J2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['J2'].font = songti_font
 
       
    sheet.merge_cells('K2:K3')
    sheet['K2'] ='重要领域网络和信息系统密码应用分类'
    sheet['K2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['K2'].font = songti_font
   
    
    sheet.merge_cells('L2:L3')
    sheet['L2'] ='功能描述'
    sheet['L2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['L2'].font = songti_font
   
    
    
    sheet.merge_cells('M2:O2')
    sheet['M2'] ='系统服务情况'
    sheet['M2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['M2'].font = songti_font
        
    sheet['M3'] ='服务范围'
    sheet['M3'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['M3'].font = songti_font
  
    
    sheet['N3'] ='服务领域'
    sheet['N3'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['N3'].font = songti_font
   
    
    sheet['O3'] ='服务对象'
    sheet['O3'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['O3'].font = songti_font
  
    
    sheet.merge_cells('P2:P3')
    sheet['P2'] ='网络覆盖情况'
    sheet['P2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['P2'].font = songti_font
 
    
    sheet.merge_cells('Q2:Q3')
    sheet['Q2'] ='系统用户数量'
    sheet['Q2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['Q2'].font = songti_font
    
    sheet.merge_cells('R2:R3')
    sheet['R2'] ='投入运行时间'
    sheet['R2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['R2'].font = songti_font
    
    sheet.merge_cells('S2:S3')
    sheet['S2'] ='系统集成外包单位名称（若有）'
    sheet['S2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['S2'].font = songti_font
  
    
    sheet.merge_cells('T2:T3')
    sheet['T2'] ='系统运维外包单位名称（若有）'
    sheet['T2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['T2'].font = songti_font
   
    
    sheet.merge_cells('U2:W2')
    sheet['U2'] ='系统等级保护测评情况'
    sheet['U2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['U2'].font = songti_font
        
    sheet['U3'] ='测评机构名称'
    sheet['U3'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['U3'].font = songti_font
    
    
    sheet['V3'] ='证书编号'
    sheet['V3'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['V3'].font = songti_font
   
    
    sheet['W3'] ='测评时间'
    sheet['W3'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['W3'].font = songti_font
    
    
    sheet.merge_cells('X2:AA2')
    sheet['X2'] ='系统密码应用安全性评估情况'
    sheet['X2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['X2'].font = songti_font
        
    sheet['X3'] ='测评机构名称'
    sheet['X3'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['X3'].font = songti_font
   
    
    sheet['Y3'] ='证书编号'
    sheet['Y3'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['Y3'].font = songti_font
  
    
    sheet['Z3'] ='测评时间'
    sheet['Z3'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['Z3'].font = songti_font
   
    
    sheet['AA3'] ='测评报告编号'
    sheet['AA3'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['AA3'].font = songti_font
   
    
    sheet.merge_cells('AB2:AG2')
    sheet['AB2'] ='系统使用密码的产品情况(台/套)'
    sheet['AB2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['AA2'].font = songti_font
        
    sheet['AB3'] ='系统使用密码产品数量'
    sheet['AB3'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['AB3'].font = songti_font
    
    sheet['AC3'] ='独立使用数量'
    sheet['AC3'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['AC3'].font = songti_font
  
    sheet['AD3'] ='共享使用数量'
    sheet['AD3'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['AD3'].font = songti_font
 
    
    sheet['AE3'] ='取得国密局审批型号产品数量'
    sheet['AE3'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['AE3'].font = songti_font
    
    sheet['AF3'] ='未取得国密局审批型号的国内产品数量'
    sheet['AF3'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['AF3'].font = songti_font

    
    sheet['AG3'] ='国外产品数量'
    sheet['AG3'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['AG3'].font = songti_font
      
    
    sheet.merge_cells('AH2:AH3')
    sheet['AH2'] ='系统中使用的密码算法'
    sheet['AH2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['AH2'].font = songti_font
   
    sheet.merge_cells('AI2:AI3')
    sheet['AI2'] ='填报日期'
    sheet['AI2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['AI2'].font = songti_font
      
def create_cp():
    
     #######设置行高
    sheet.row_dimensions[1].height = 26   # 第1行行高
    sheet.row_dimensions[2].height = 29   # 第2行行高
    

    ########## 设置列宽
    sheet.column_dimensions['A'].width = 3
    sheet.column_dimensions['B'].width = 6  # B列列宽
    sheet.column_dimensions['C'].width = 15  # C列列宽
    sheet.column_dimensions['D'].width = 18  # C列列宽
    sheet.column_dimensions['E'].width = 15  # C列列宽
    sheet.column_dimensions['F'].width = 22  # C列列宽
    sheet.column_dimensions['G'].width = 19  # C列列宽
    sheet.column_dimensions['H'].width = 18  # C列列宽
    sheet.column_dimensions['I'].width = 13  # C列列宽
    sheet.column_dimensions['J'].width = 16  # C列列宽
    sheet.column_dimensions['K'].width = 13  # C列列宽
    sheet.column_dimensions['L'].width = 10  # C列列宽
    sheet.column_dimensions['M'].width = 14  # C列列宽
    sheet.column_dimensions['N'].width = 20 # C列列宽
    sheet.column_dimensions['O'].width = 32  # C列列宽
  
    
    sheet.merge_cells('B1:D1')
    sheet['B1'] ='应用系统密码产品使用情况表：'
    sheet['B1'].alignment = Alignment(horizontal='center', vertical='center')  #居中
    songti_font = Font(name='宋体', size=12, italic=False, color=colors.BLACK, bold=True)
    sheet['B1'].font = songti_font
    
    
    
    sheet.merge_cells('B2:B3')
    sheet['B2'] ='序号'
    sheet['B2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['B2'].font = songti_font
   

    sheet.merge_cells('C2:C3')
    sheet['C2'] ='所属省部'
    sheet['C2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['C2'].font = songti_font
    
    sheet.merge_cells('D2:D3')
    sheet['D2'] ='归口密码管理部门'
    sheet['D2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['D2'].font = songti_font
    
    sheet.merge_cells('E2:E3')
    sheet['E2'] ='所属单位'
    sheet['E2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['E2'].font = songti_font
    
    sheet.merge_cells('F2:F3')   
    sheet['F2'] ='产品所属系统'
    sheet['F2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['F2'].font = songti_font


    sheet.merge_cells('G2:G3')
    sheet['G2'] ='密码产品名称'
    sheet['G2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['G2'].font = songti_font
    
    sheet.merge_cells('H2:H3')
    sheet['H2'] ='产品类别'
    sheet['H2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['H2'].font = songti_font
   
    sheet.merge_cells('I2:I3')
    sheet['I2'] ='型号'
    sheet['I2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['I2'].font = songti_font
    

    sheet.merge_cells('J2:J3')
    sheet['J2'] ='生产厂商'
    sheet['J2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['J2'].font = songti_font
  
    sheet.merge_cells('K2:K3')
    sheet['K2'] ='是否共享使用'
    sheet['K2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['K2'].font = songti_font

    
    sheet.merge_cells('L2:L3')
    sheet['L2'] ='数量(台/套)'
    sheet['L2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['L2'].font = songti_font

    sheet.merge_cells('M2:M3')
    sheet['M2'] ='商密型号'
    sheet['M2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['M2'].font = songti_font
    
    sheet.merge_cells('N2:N3')
    sheet['N2'] ='使用的密码算法'
    sheet['N2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['N2'].font = songti_font
    
    sheet.merge_cells('O2:O3')
    sheet['O2'] ='主要用途'
    sheet['O2'].alignment = Alignment(horizontal='center', vertical='center')
    songti_font = Font(name='宋体', size=10, italic=False, color=colors.BLACK, bold=False)
    sheet['O2'].font = songti_font
    
    sheet['P3']=''

#新建excel 保存汇总
#=============================================================================

wb_sum = openpyxl.Workbook() 

#汇总 单位
#=============================================================================

path_dw = dw_goalpath
path_dw_list = os.listdir(path_dw)

sheet = wb_sum.active
sheet.title ="单位列表" 
dw_xuhao = 0
create_dw()   #######s设计表格

for filename in path_dw_list:   #遍历所有的excel
    f_dw= open(os.path.join(path_dw,filename),'rb') #打开
    df_dw= pd.read_excel(f_dw)
    data_dw= df_dw.values  # 获取所有的数据
    
    data_dw_2=data_dw[2].tolist() #把第三行的数转化为列表
    sheet.append(data_dw_2) #写入第三行数据
    dw_xuhao=dw_xuhao +1
    
    f_dw.close()
    
    dw_file_path = os.path.join(path_dw, filename)
    
    if os.path.isfile(dw_file_path):
        os.remove(dw_file_path)
        
    #elif os.path.isdir(dw_file_path):
    #    shutil.rmtree(dw_file_path)
    
for i in range(0,dw_xuhao):   

    sheet.cell(row=i+4,column=2,value=i+1) #序号
  
# 汇总 系统
#=============================================================================
path_xt = xt_goalpath
path_xt_list = os.listdir(path_xt)
sheet =wb_sum.create_sheet('系统列表')  #创建系统sheet

xt_xuhao=0  #XT 序号

create_xt()

for filename in path_xt_list:   #遍历所有的excel
    f_xt= open(os.path.join(path_xt,filename),'rb') #打开
    df_xt= pd.read_excel(f_xt)
    data_xt= df_xt.values  # 获取所有的数据
    data_xt_2 =data_xt[2].tolist() #把第三行的数转化为列表
    
    sheet.append(data_xt_2) #写入第三行数据
    xt_xuhao=xt_xuhao +1
    
    f_xt.close()
    
    xt_file_path = os.path.join(path_xt,filename) #删除中间文件
    if os.path.isfile(xt_file_path):
        os.remove(xt_file_path)
    
for i in range(0,xt_xuhao):   #写入序号

    sheet.cell(row=i+4,column=2,value=i+1) #序号
   
# 汇总：产品
#=============================================================================
path_cp = cp_goalpath   #找到cp中间文件路径
path_cp_list = os.listdir(path_cp)
sheet = wb_sum.active
sheet = wb_sum.create_sheet('产品列表')
cp_xuhao =0
create_cp()

for filename in path_cp_list:   #遍历所有的excel
    f_cp= open(os.path.join(path_cp,filename),'rb') #打开
    df_cp = pd.read_excel(f_cp)
    data_cp = df_cp.values  # 获取所有的数据
    
    f_cp.close()
    
    cp_file_path = os.path.join(path_cp, filename)  #删除中间数据
    if os.path.isfile(cp_file_path):
       os.remove(cp_file_path)
    
    for i in range(2,len(data_cp)):
    
       data_cp_2 = data_cp[i].tolist() #把第三行的数转化为列表
       sheet.append(data_cp_2) #写入第三行数据
       
       cp_xuhao=cp_xuhao+1  #记录量  放序号
              
for i in range(0,cp_xuhao):   #写入序号
    sheet.cell(row=i+4,column=2,value=i+1) #序号
 
#打印总数并汇总
#=============================================================================
wb_sum.save(sum_goalpath)  ####保存汇总表 

shutil.rmtree('./临时文件')   ####删除临时文件
#shutil.rmtree('./临时文件/系统汇总/')



print('汇总完成：汇总表.xlsx 已经生成')
print('单位总数:' +str(dw_xuhao))
print('系统总数:' +str(xt_xuhao))
print('产品总数:' +str(cp_xuhao))     

endtime = time.time()
dtime = endtime - starttime

print("程序运行时间：%.8s s" % dtime)  #显示到微秒    
       
        
        
      
             
        
        
        
            
           
          
    
         
           
        
        
        
        
      
            
        
        